﻿using HCQ2_Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HCQ2_IDAL;
using HCQ2_Model.SelectModel;
using System.Data;
using HCQ2_Model.DocModel;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRCustomPassWordDAL: IT_VRCustomPassWordDAL
    {
        /// <summary>
        ///  获取关卡（课件id）
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public List<SelectModel> GetPassData(int file_id)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT CAST(cp_id AS NVARCHAR(10)) AS value,cp_name AS text FROM T_VRCustomPass WHERE file_id={0};", file_id);
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<SelectModel>(dt);
        }
        /// <summary>
        ///  获取关卡单词列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<HeadWordModel> GetTableData(HeadWordParam model, out int total)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT TOP {0} * FROM 
            (SELECT ROW_NUMBER() OVER(ORDER BY pw_id ASC) AS rowNumer,pw_id,cp_id,cp_name,file_id,file_name,word,gj_symbol,kk_symbol,cp_type,china_mean,create_name,CONVERT(varchar(100),GETDATE(),120) as create_date,update_name,CONVERT(varchar(100),GETDATE(),120) as update_date,cp_note
             FROM T_VRCustomPassWord WHERE 1=1  ", model.rows);
            if (model.file_id > 0)
                sb.AppendFormat(" AND file_id={0} ", model.file_id);
            else
                sb.AppendFormat(" AND 1=0 ");
            if(model.cp_id>0)
                sb.AppendFormat(" AND cp_id={0} ", model.cp_id);
            if(!string.IsNullOrEmpty(model.keyword))
                sb.AppendFormat(" AND word='{0}' ", model.keyword);
            sb.AppendFormat(")word WHERE word.rowNumer>{0};", (model.page - 1) * model.rows);
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            total = GetTableCount(model);
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<HeadWordModel>(dt);
        }
        private int GetTableCount(HeadWordParam model)
        {
            sb?.Clear();
            sb.AppendFormat("SELECT COUNT(0) AS num FROM T_VRCustomPassWord WHERE 1=1 ");
            if (model.file_id > 0)
                sb.AppendFormat(" AND file_id={0} ", model.file_id);
            if (model.cp_id > 0)
                sb.AppendFormat(" AND cp_id={0} ", model.cp_id);
            if (!string.IsNullOrEmpty(model.keyword))
                sb.AppendFormat(" AND word='{0}' ", model.keyword);
            return HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString()));
        }
    }
}
